<?php 
/**
 * @file db.class.php
 * @package taobaoke
 * @subpackage 
 * @version $id: 0.1 UTF-8 2009-7-16 下午01:30:34 $
 * @author LD King (mifunny.info)
 * @copyright Copyright (c) 2007 - 2009 MiFunny China Inc. (http://mifunny.info)
 * 有关处理MySQL数据的信息
 */
require_once dirname(__FILE__).'/../config.inc.php';

class tbkDB{
	
	private $dbh;
	
	/**
	 * 链接数据库
	 */
	public function __construct($long = false){
		try{ //数据库保持长连接
			if ($long) {
				$this->dbh = new PDO('mysql:host='.DB_HOST.';dbname='.DB_NAME, DB_USER, DB_PASSWORD, array(PDO::ATTR_PERSISTENT => true));
			}else 
				$this->dbh = new PDO('mysql:host='.DB_HOST.';dbname='.DB_NAME, DB_USER, DB_PASSWORD); //, array(PDO::ATTR_PERSISTENT => true));
			$this->dbh->query('set names '.DB_CHARSET.';');
		}catch(Exception $e){
   			die( "MySQL Conneting Failed: " . $e->getMessage() );
		}
	}//END func __construct();
	
	public function __destruct(){
		unset($this->dbh);
	}//END func __destruct
	
	/**
	 * 返回一条随机商品信息
	 * 
	 * @return Array $item
	 */
	public function getItemRand(){
		try {
			$queryItem = "SELECT `iid` , `is` , `commission_num` , `title` , `title2` , `pic_url` , `price` , `click_url` , `nick` , `cid` , `location` , `properties` , `desc`
				FROM `".DB_NAME."`.`items` AS t1
					JOIN (
						SELECT round( rand( ) * (
							SELECT max( id )
								FROM `".DB_NAME."`.`items` ) ) AS id
						) AS t2
				WHERE t1.id >= t2.id
					AND `is` != '1'
					ORDER BY t1.id
				LIMIT 1";
			$item = $this->dbh->query($queryItem)->fetch(PDO::FETCH_ASSOC);
			
			$queryShop = "SELECT `nick` , `credit` , `sex` , `title` , `desc` , `created` , `click_url`
				FROM `".DB_NAME."`.`shops` WHERE `nick` = '".$item['nick']."' ;";
			$shop = $this->dbh->query($queryShop)->fetch(PDO::FETCH_ASSOC);
			$item['shop_credit'] = $shop['credit'];
			$item['shop_sex'] = $shop['sex'];
			$item['shop_title'] = $shop['title'];
			$item['shop_desc'] = $shop['desc'];
			$item['shop_created'] = $shop['created'];
			$item['shop_click_url'] = $shop['click_url'];
			
			$queryCat = "SELECT `categories`.`name` AS `cname` , `tRule`.`sid` AS `sid`
						FROM `tRule` , `categories`
						WHERE `categories`.`rid` = `tRule`.`ID`
						AND `categories`.`cid` = '".$item['cid']."' LIMIT 1 ;";
			$cat = $this->dbh->query($queryCat)->fetch(PDO::FETCH_ASSOC);
			$item['cname'] = $cat['cname'];
			$item['sid'] = $cat['sid'];
			
		}catch (Exception $e){
			exit("<br /><span style=\"color:red\">getItemRand()查询出错</span>: ".$e->getMessage());
		}
		return $item;
	}//END func getItemRand
	
	
	/**
	 * 得到所有商铺的卖家昵称
	 * 
	 * @return Array $result
	 */
	public function getShopsNick(){
		try {
			$query = "SELECT `nick` FROM `".DB_NAME."`.`shops` ;";
			$result = $this->dbh->query($query)->fetchAll(PDO::FETCH_ASSOC);
		}catch (Exception $e){
			exit("<br /><span style=\"color:red\">getShopsNick()查询出错</span>: ".$e->getMessage());
		}
		return $result;
	}//END func getItemIid
	
	/**
	 * 得到所有未发布商品的ID
	 * 
	 * @param INT $num
	 * @return Array $result
	 */
	public function getItemsIid($num = 30){
		try {
			$query = "SELECT `iid` FROM `".DB_NAME."`.`items` WHERE `is` != '1' ORDER BY RAND() LIMIT 0 , ".$num." ;";
			$result = $this->dbh->query($query)->fetchAll(PDO::FETCH_ASSOC);
		}catch (Exception $e){
			exit("<br /><span style=\"color:red\">getItemsIid()查询出错</span>: ".$e->getMessage());
		}
		return $result;
	}//END func getItemIid
	
	/**
	 * 从数据库中获得单个商品的详细信息
	 * 
	 * @param String $iid
	 * @return Array $item
	 */
	public function getItemInfo($iid){
		try {
			$queryItem = "SELECT `iid` , `is` , `commission_num` , `title` , `title2` , `pic_url` , `price` , `click_url` , `nick` , `cid` , `location` , `properties` , `desc`
				FROM `".DB_NAME."`.`items` WHERE `iid` = '{$iid}' ;";
			$item = $this->dbh->query($queryItem)->fetch(PDO::FETCH_ASSOC);
			
			$queryShop = "SELECT `nick` , `credit` , `sex` , `title` , `desc` , `created` , `click_url`
				FROM `".DB_NAME."`.`shops` WHERE `nick` = '".$item['nick']."' ;";
			$shop = $this->dbh->query($queryShop)->fetch(PDO::FETCH_ASSOC);
			$item['shop_credit'] = $shop['credit'];
			$item['shop_sex'] = $shop['sex'];
			$item['shop_title'] = $shop['title'];
			$item['shop_desc'] = $shop['desc'];
			$item['shop_created'] = $shop['created'];
			$item['shop_click_url'] = $shop['click_url'];
			
			$queryCat = "SELECT `categories`.`name` AS `cname` , `tRule`.`sid` AS `sid`
						FROM `tRule` , `categories`
						WHERE `categories`.`rid` = `tRule`.`ID`
						AND `categories`.`cid` = '".$item['cid']."' LIMIT 1 ;";
			$cat = $this->dbh->query($queryCat)->fetch(PDO::FETCH_ASSOC);
			$item['cname'] = $cat['cname'];
			$item['sid'] = $cat['sid'];
		}catch (Exception $e){
			exit("<br /><span style=\"color:red\">getItemInfo(".$iid.")查询出错</span>: ".$e->getMessage());
		}
		return $item;
	}//END func getItemInfo

	/**
	 * 获得分类的信息(true时使用 parent_cid查询, false时使用cid查询)
	 * @param $cid
	 * @param $is 
	 * @return Array $result
	 */
	public function getCatInfo($cid = 0, $is = true){
		try {
			$query = "SELECT `cid` , `parent_cid` , `name` , `rid` , `is_parent` FROM `".DB_NAME."`.`categories` WHERE ";
			if($is){
				$query .= " `parent_cid` = '{$cid}' ;";
			}else {
				$query .= " `cid` = '{$cid}' ;";
			}
			$result = $this->dbh->query($query)->fetchAll(PDO::FETCH_ASSOC);
		}catch (Exception $e){
			exit("<br /><span style=\"color:red\">getCatInfo({$cid},{$is})查询出错</span>: ".$e->getMessage());
		}
		return $result;
	}//END func getCatInfo
	
	/**
	 * 得到规则信息
	 */
	public function getRuleInfo($rid = 0){
		try {
			if ($rid == 0)
				 $query1 = "SELECT `ID` AS `rid` , `name` , `sid` FROM `".DB_NAME."`.`tRule` ;";
			else
				$query1 = "SELECT `ID` AS `rid` , `name` , `sid` FROM `".DB_NAME."`.`tRule` WHERE `ID` = `{$rid}` ;";
			$result = $this->dbh->query($query1)->fetchAll(PDO::FETCH_ASSOC);
			
			if (!empty($result)){
				for ($i=0; $i<count($result); $i++){
					$query2 = "SELECT `cid` , `name` FROM `".DB_NAME."`.`categories` 
						WHERE `parent_cid` = '0' AND `rid` = '{$result[$i]['rid']}'";
					$re2 = $this->dbh->query($query2)->fetchAll(PDO::FETCH_ASSOC);
					$result[$i]['cats'] = $re2;  //获得支持规则的分类

					$query3 = "SELECT `ID` AS `tid` , `name` FROM `".DB_NAME."`.`tTag` WHERE `rid` = '{$result[$i]['rid']}'
						ORDER BY `created` DESC LIMIT 0 , 30";
					$re3 = $this->dbh->query($query3)->fetchAll(PDO::FETCH_ASSOC);
					$result[$i]['tags'] = $re3; //获得规则关键字
				}
			} // if (!empty($result))
				
		}catch (Exception $e){
			exit("<br /><span style=\"color:red\">getRuleInfo({$rid})查询出错</span>: ".$e->getMessage());
		}
		return $result;
	} //END func getRuleInfo
	
	/**
	 * 测试数据是否存在
	 * @param $iid
	 * @param $table
	 * @return boolean
	 */
	public function isExist($iid,$table){
		if($table == 'shops'){
			$sqlSelect = "SELECT `user_id` FROM `".$table."` WHERE `user_id` = '".$iid."' and TO_DAYS(NOW()) - TO_DAYS(`time`) < 15;";
		}elseif ($table == 'categories'){
			$sqlSelect = "SELECT `cid` FROM `".$table."` WHERE `cid` = '".$iid."' ;";
		}else {
			$sqlSelect = "SELECT `iid` FROM `".$table."` WHERE `iid` = '".$iid."' ;";
		}
		$result = $this->dbh->query($sqlSelect);
		if ($result){ //结果集不为空
			$row = $result->fetch(PDO::FETCH_BOTH);
		
			if (empty($row[0])){
				/*if($table == 'shop')
					$this->exec("DELETE FROM `shops` WHERE `user_id` = '".$iid.";");*/
				return 0; //未含有条目
			}else{
				return 1; //含有条目
			}
		}else return 0;
	}//END func isExist

	/**
	 * 更新单条记录
	 * 
	 * @param Array $update
	 * 					array('table','ID','key','row','value')
	 * 							表,	查询字段,待查值, 更新字段,更新值
	 */
	public function updateRow($update){
		try {
			$query = "UPDATE `".DB_NAME."`.`{$update['table']}` SET `{$update['row']}` = '{$update['value']}' 
					WHERE `{$update['table']}`.`{$update['ID']}` = '{$update['key']}' ;";
			//var_dump($query);
			$this->dbh->query($query);
		}catch (Exception $e){
			exit("<br /><span style=\"color:red\">updateRow()更新失败</span>: ".$e->getMessage());
		}
	}//END func updateRow
	
	/**
	 * 更新单条记录
	 * 
	 * @param Array $update
	 * 					array('table','row','value')
	 * 							表,	查询字段,待查值, 更新字段,更新值
	 */
	public function deleteRow($update){
		try {
			$query = "DELETE FROM `".DB_NAME."`.`{$update['table']}`
					WHERE `{$update['table']}`.`{$update['row']}` = '{$update['value']}' ;";
			$this->dbh->query($query);
		}catch (Exception $e){
			exit("<br /><span style=\"color:red\">deleteRow()删除失败</span>: ".$e->getMessage());
		}
	}//END func updateRow
	
	/**
	 * 商品入库
	 */
	public function inItem($item, $is = false){
		try {
		$query1 = "INSERT INTO `".DB_NAME."`.`items` (`iid`, `title`, `title2`, `pic_url`, `price`, `click_url`, 
				`nick`, `cid`,`location`, `properties`, `desc`,
				`commission`, `commission_rate`, `commission_num`, `commission_volume`";
				
		$query2 = ") VALUES ('".$item['iid']."', '".$item['title']."', '".$item['title2']."', '".$item['pic_url']."', '".
				$item['price']."', '".$item['click_url']."', '".$item['nick']."', '".$item['cid']."', '".
				$item['location']."', '".$item['properties']."', '".$item['desc']."', '".$item['commission']."', '".
				$item['commission_rate']."', '".$item['commission_num']."', '".$item['commission_volume']."'";
		if ($is){
			$query1 .= ", `".is."`";
			$query2 .= ", '2'";	
		}
		$query = $query1.$query2.") ;";
				
		//echo $query;
		$this->dbh->query($query);
		}catch (Exception $e){
			echo "<br /><span style=\"color:red\">Item(".$item['iid'].")入库失败</span>: ".$e->getMessage();
		}
	}//END func 
	
	/**
	 * 商店入库
	 */
	public function inShop($shop){
		try {
			$query = "INSERT INTO `".DB_NAME."`.`shops` (`user_id`, `nick`, `credit`, `sex`, 
				`sid`, `title`, `desc`, `created`, `click_url`, `shop_commission.rate`) VALUES ('".
				$shop['user_id']."', '".$shop['nick']."', '".$shop['credit']."', '".$shop['sex']."', '".
				$shop['sid']."', '".$shop['title']."', '".$shop['desc']."', '".$shop['created']."', '".
				$shop['click_url']."', '".$shop['shop_commission.rate']."')";
			//echo $query;
			$this->dbh->beginTransaction();
			$this->dbh->exec("DELETE FROM `".DB_NAME."`.`shops` WHERE `user_id` = '".$shop['user_id']."' ;");
			$this->dbh->exec($query);
			$this->dbh->commit();
		}catch (Exception $e){
			$this->dbh->rollBack();	
			echo "<br /><span style=\"color:red\">Shop(".$shop['user_id'].")入库失败</span>: ".$e->getMessage();
		}
	}//END func 
	
	/**
	 * 分类入库
	 * @param array $cats
	 */
	public function inCategories($cats){
		try {
			$this->dbh->beginTransaction();
			//$this->dbh->exec("TRUNCATE TABLE `".DB_NAME."`.`categories` ;");
		
			foreach ($cats as $cat){
				if ($this->isExist($cat['cid'], 'categories'))
					$this->dbh->exec("DELETE FROM `".DB_NAME."`.`categories` WHERE `cid` = '".$cat['cid']."' ;");
				$query = "Insert INTO `".DB_NAME."`.`categories` (`cid`, `parent_cid`, `name` ,
					 `is_parent`, `status` ,`sort_order`) VALUES (";
				foreach($cat as $k => $v){
					if($k == 'is_parent'){
						if($v ==  'true') $v = 1;
						else $v = 0;
					}
					if($k == 'sort_order')
						$query .= "'".$v."'";
					else
						$query.= "'".$v."', ";
				}
				$query .= " ) ;";
				$this->dbh->exec($query);
			}  
			
			$this->dbh->commit();
		}catch (Exception $e){
			$this->dbh->rollBack();	
			echo "<br /><span style=\"color:red\">Categories入库失败</span>: ".$e->getMessage();
		}
	}//END func inCategories
	
	/**
	 * 更新规则对应的淘宝分类
	 * 
	 * @param $rid 规则ID
	 * @param $cid 分类ID
	 */
	public function inRuleCat($rid,$cid){
		$this->updateRow(array('table' => 'categories',
								'ID' => 'cid',
								'key' => $cid,
								'row' => 'rid',
								'value' => $rid));
		$cats = $this->getCatInfo($cid);
		foreach ($cats as $cat){
			if ($cat['is_parent'])
				$this->inRuleCat($rid,$cat['cid']);
			else 
				$this->updateRow(array('table' => 'categories',
								'ID' => 'cid',
								'key' => $cat['cid'],
								'row' => 'rid',
								'value' => $rid));
		} //foreach ($cats as $cat)
	}//END func inRuleCat
	
	/**
	 * 插入规则关键字
	 */
	public function inRuleTag($rid,$tag){
		try {
			$query = "SELECT `ID` FROM `".DB_NAME."`.`tTag` WHERE `name` = '{$tag}' ;";
			$re = $this->dbh->query($query);
			$tid = $re->fetch(PDO::FETCH_ASSOC);
			if (!empty($tid['ID'])){
				$this->updateRow(array('table' => 'tTag',
								'ID' => 'ID',
								'key' => $tid['ID'],
								'row' => 'created',
								'value' => date("Y-m-d H:i:s",time()) 
							));
			}else {
				$query = "INSERT INTO `".DB_NAME."`.`tTag` (`ID`, `name`, `rid`, `created`)
					 VALUES (NULL, '{$tag}', '{$rid}', CURRENT_TIMESTAMP);";
				$this->dbh->query($query);
			}
		}catch (Exception $e){
			exit("<br /><span style=\"color:red\">inRuleTag({$rid}, {$tag})入库失败</span>: ".$e->getMessage());
		}
	} //END func inRuleTag
	
	/**
	 * 把发布Delicious所需要的信息存入数据中...
	 *
	 * @param Array $delicious
	 */
	function inDelicious($delicious, $status){
		
		try {
			$this->dbh->beginTransaction();
			
			if ( $this->isExist($delicious['iid'], 'tURL') ) {
				$query = "DELETE FROM `tURL` WHERE `iid` = '{$delicious['iid']}'";
				$this->dbh->exec($query);
			}
			
			if ( $status == 3 ){
				$query = "INSERT INTO `".DB_NAME."`.`tURL` ( `wp_id`, `iid`, `title`, `desc`, `tags`, `url`,`status`, `content` )
					VALUES ( '{$delicious['wp_id']}', '{$delicious['iid']}', '{$delicious['title']}',
					'{$delicious['desc']}', '{$delicious['tags']}', '{$delicious['url']}', '{$status}', '{$delicious['content']}' );";
			}else 
				$query = "INSERT INTO `".DB_NAME."`.`tURL` ( `wp_id`, `iid`, `title`, `desc`, `tags`, `url`,`status` )
					VALUES ( '{$delicious['wp_id']}', '{$delicious['iid']}', '{$delicious['title']}',
					'{$delicious['desc']}', '{$delicious['tags']}', '{$delicious['url']}', '{$status}' );";
			$this->dbh->exec($query);
			
			$this->dbh->commit();
			return true;
		}catch (Exception $e){
			$this->dbh->rollBack();	
			exit("<br /><span style=\"color:red\">inDelicious()入库失败</span>: ".$e->getMessage());
		}
		return false;
	}//END func inDelicious
	
	
	/**
	 * 直接存入文章
	 *
	 * @param Array $post //文章信息
	 * @return bool 为true则发布成功
	 */
	function inPost($post){
		try {
			global $table_prefix;
			// 插入文章
			$queryPost ="INSERT INTO `".$table_prefix."posts` ( `post_author`, `post_date`, `post_date_gmt`, 
					`post_content`, `post_title`, `post_excerpt`, `post_status`, 
					`comment_status`, `post_name`, `post_modified`, `post_modified_gmt`,
					 `post_parent`, `post_type`, `comment_count`) 
					VALUES ('{$post['post_author']}', '{$post['post_date']}', '{$post['post_date']}',
					'{$post['post_content']}', '{$post['post_title']}', '{$post['post_excerpt']}', '{$post['post_status']}',
					'open', '{$post['post_name']}','{$post['post_date']}', '{$post['post_date']}',
					'0', 'post', '0') ;";
			$this->dbh->query($queryPost);
			$ID = $this->dbh->lastInsertId();
			// 插入分类
			$queryCat = "INSERT INTO `".$table_prefix."term_relationships` (object_id ,term_taxonomy_id ) VALUES ";
			for ($i=1; $i< count($post['post_category']); $i++) {
				$queryCat .= " ('{$ID}', '{$post['post_category'][$i]}'),";
			}
			$queryCat .= " ('{$ID}', '{$post['post_category'][0]}') ;";
			$this->dbh->query($queryCat);
			//插入自定义字段
			if (!empty($post['post_meta'])){
				$queryMeta = "INSERT INTO `".$table_prefix."postmeta` (post_id ,meta_key ,meta_value ) 
						VALUES ('{$ID}','price','{$post['post_meta']['price']}'),
						('{$ID}','pic_url','{$post['post_meta']['pic_url']}') ;";
				$this->dbh->query($queryMeta);
			}
			
			// 插入关键字
			foreach($post['tags_input'] as $tag)
			{
				$tag =trim($tag);
				$ssql="SELECT `term_id` FROM `".$table_prefix."terms` WHERE name = '{$tag}' ;";
				$result = $this->dbh->query($ssql);
				$srow = $result->fetch(PDO::FETCH_BOTH);
				
				if ( empty($srow[0]) ){ //不存在，加入
					$tagName = strtolower(sanitize_title($tag));
					$sqlAddTag = "INSERT INTO `".$table_prefix."terms` (`name`,`slug`) VALUES ('{$tag}','{$tagName}')";
					$this->dbh->query( $sqlAddTag );
					$tagTermID = $this->dbh->lastInsertId();
					
					$sqlAddTerm="INSERT INTO `".$table_prefix."term_taxonomy` (`term_id`, `taxonomy`) 
								VALUES ('{$tagTermID}','post_tag')";
					$this->dbh->query( $sqlAddTerm );
					$tagTermTaxonomyID = $this->dbh->lastInsertId();
					
					$sqlAddTermRelationships = "INSERT INTO `".$table_prefix."term_relationships` (object_id ,term_taxonomy_id ) 
								VALUES ('{$ID}', '{$tagTermTaxonomyID}')";
					$this->dbh->query( $sqlAddTermRelationships );
					
				}else { //存在，建立链接
					$ssql = "SELECT `term_taxonomy_id`, `term_id` FROM `".$table_prefix."term_taxonomy` WHERE `term_id` = '{$srow['term_id']}' ;";
					$result = $this->dbh->query( $ssql );
					$tagid = $result->fetch(PDO::FETCH_ASSOC);
					$ssql = "INSERT INTO `".$table_prefix."term_relationships` (`object_id`, `term_taxonomy_id` ) 
							VALUES ('{$ID}','{$tagid['term_taxonomy_id']}')";
					$this->dbh->query( $ssql );
				}
				
			}//END 插入关键字
			return true;
		}catch (Exception $e){
			exit("<br /><span style=\"color:red\">inPost()插入失败</span>: ".$e->getMessage());
		}
		return false;
	} //END func inPost

}//END class tbkDB
//FILE END
?>